package cn.anosi.repository.auth;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import cn.anosi.entity.auth.Permission;
import cn.anosi.entity.auth.User;

public interface UserRepository extends JpaRepository<User, Integer>,JpaSpecificationExecutor<User>{
	/**
	 * 根据用户名和密码查询用户
	 * @param username
	 * @param password
	 * @return
	 */
	public User findByUsernameAndPassword(String username, String password);

	/**
	 * 账户启用、禁用
	 * @param id 用户id
	 * @param locked 启用、禁用
	 * @return
	 */
	@Modifying
	@Query("update User u set u.locked = ?2 where u.id = ?1")
	public int lock(Integer id, String locked);

	/**
	 * 查询用户并分页
	 * @param name 
	 * @param pageable
	 * @return
	 */
	@Query("select u from User u where u.username like %?1% or u.realname like %?1%")
	public List<User> users(String name, Pageable pageable);

	/**
	 * 批量禁用用户
	 * @param ids
	 * @return
	 */
	@Modifying
	@Query("update User u set u.locked = 1 where u.id in (?1)")
	public int batchDel(List<Integer> ids);
	
	/**
	 * 查询用户的菜单列表
	 * @param id
	 * @return
	 */
	@Query(value = "select distinct p from RolePermission rp left join rp.permission p where rp.role.id in (select ur.role.id from UserRole  ur where ur.user.id = ?) and p.type = 'menu' order by p.sortId desc")
	public List<Permission> menus(Integer id);
	
	/**
	 * 查询用户的所有权限
	 * @param id
	 * @return
	 */
	@Query(value = "select distinct p from RolePermission rp left join rp.permission p where rp.role.id in (select ur.role.id from UserRole  ur where ur.user.id = ?) and p.type = 'permission'")
	public List<Permission> permissions(Integer id);

	public User findByUsernameAndLocked(String username, String locked);
	
	/**
	 * 查询用户的角色名
	 * @param id
	 * @return
	 */
	@Query(value="select role.name from role where role.id in (SELECT role_id from user_role ur where ur.user_id = ?)", nativeQuery = true)
	public List<String> findUserRoleNames(Integer id);

	@Modifying
	@Query("update User u set u.tx = ?2 where u.id = ?1")
	public int update(Integer id, byte[] img);

	public User getById(Serializable id);

}
